import csv
import io
import chardet
from flask import Blueprint, jsonify, request
from flask_jwt_extended import jwt_required
from libs.conn import execute, fetchone, fetchvalue, fetchall, system_log, device_log, create_conn, export_data
from libs.utils import generate_random_string, validate_client_id, is_valid_ip_or_domain, get_time

clients_bp = Blueprint('clients', __name__)


@clients_bp.route('/api/get_clients', methods=['GET'])
@jwt_required()
def get_client_list():
    client_id = request.args.get('client_id')
    client_name = request.args.get('client_name')
    client_ip = request.args.get('client_ip')
    client_type = request.args.get('client_type')
    client_check_time = request.args.get('client_check_time')
    client_alive_time = request.args.get('client_alive_time')
    page_size = int(request.args.get('page_size'))
    page_current = int(request.args.get('page_current'))
    sql = "FROM clients c left join client_infos ci on c.client_id == ci.client_id WHERE 1=1"
    params = []
    if client_id != None:
        sql += " AND c.client_id = ?"
        params.append(client_id)
    if client_name != None:
        sql += " AND client_name like ?"
        params.append('%'+client_name+'%')
    if client_ip != None:
        sql += " AND client_ip like ?"
        params.append('%'+client_ip+'%')
    if client_type != None:
        sql += " AND client_type like ?"
        params.append('%'+client_type+'%')
    if client_check_time != None:
        sql += " AND client_check_time like ?"
        params.append('%'+client_check_time+'%')
    if client_alive_time != None:
        sql += " AND client_alive_time like ?"
        params.append('%'+client_alive_time+'%')
    offset = (page_current - 1) * page_size
    rows = fetchall("SELECT id, c.client_id, client_name, client_ip, client_type, client_info, client_check_time, client_alive_time, model, api_enable, port, username, password, CASE WHEN client_secret IS NULL THEN 0 ELSE 1 END AS has_secret " + sql + " ORDER BY id DESC LIMIT ? OFFSET ?", tuple(params + [page_size, offset]))
    total_count = fetchvalue("SELECT COUNT(*) " + sql, tuple(params))
    return { 'status': 'ok', 'data': rows, 'page_total': total_count }


# 批量导出设备
@clients_bp.route('/api/export_clients')
@jwt_required()
def export_clients():
    client_id = request.args.get('client_id')
    client_name = request.args.get('client_name')
    client_ip = request.args.get('client_ip')
    client_type = request.args.get('client_type')
    client_check_time = request.args.get('client_check_time')
    client_alive_time = request.args.get('client_alive_time')
    sql = "FROM clients WHERE 1=1"
    params = []
    if client_id != None:
        sql += " AND client_id = ?"
        params.append(client_id)
    if client_name != None:
        sql += " AND client_name like ?"
        params.append('%'+client_name+'%')
    if client_ip != None:
        sql += " AND client_ip like ?"
        params.append('%'+client_ip+'%')
    if client_type != None:
        sql += " AND client_type like ?"
        params.append('%'+client_type+'%')
    if client_check_time != None:
        sql += " AND client_check_time like ?"
        params.append('%'+client_check_time+'%')
    if client_alive_time != None:
        sql += " AND client_alive_time like ?"
        params.append('%'+client_alive_time+'%')
    conn, cursor = create_conn()
    cursor.execute('SELECT client_id, client_name, client_ip, client_type, client_info, client_check_time, client_alive_time ' + sql + " LIMIT 50000", tuple(params))
    rows = cursor.fetchall()
    if len(rows) > 0:
        file_name = export_data(cursor, rows)
        cursor.close()
        conn.close()
        system_log('导出日志文件：' + file_name)
        return { 'status': 'ok', 'data': file_name }
    else:
        cursor.close()
        conn.close()
        return { 'status': 'error', 'data': '没有数据' }
    

# 批量导入设备
@clients_bp.route('/api/import_clients', methods=['POST'])
@jwt_required()
def import_clients():
    file = request.files['file']
    if not file.filename.endswith('.csv'):
        return { 'status': 'error', 'data': '文件格式不正确，只支持csv格式' }
    conn, cursor = create_conn()
    try:
        # 使用 StringIO 来处理 CSV 内容
        content = file.stream.read()
        encoding = chardet.detect(content)['encoding']
        stream = io.StringIO(content.decode(encoding))
        # 读取 CSV 数据
        reader = csv.reader(stream)
        # 跳过表头
        next(reader)
        # 插入数据到数据库
        success_count = 0
        for row in reader:
            client_id = row[0]
            client_ip = row[2]
            if validate_client_id(client_id) == False:
                return { 'status': 'error', 'data': '导入失败：第 ' + str(reader.line_num) + ' 行的 client_id 格式不正确' }
            if client_ip != None and client_ip != '' and is_valid_ip_or_domain(client_ip) == False:
                return { 'status': 'error', 'data': '导入失败：第 ' + str(reader.line_num) + ' 行的 client_ip 格式不正确' }
            cursor.execute("SELECT COUNT(*) FROM clients WHERE client_id = ?", (client_id,))
            if cursor.fetchone()[0] > 0: # 如果已存在则不再插入
                continue
            cursor.execute('INSERT INTO clients (client_id, client_name, client_ip, client_type, client_info) VALUES (?, ?, ?, ?, ?)', (*row,))
            cursor.execute('INSERT INTO logs (client_id, log_time, log_content) VALUES (?, ?, ?)', (client_id, get_time(), '设备添加成功（批量添加）'))
            success_count += 1
        # 提交事务并关闭连接
        conn.commit()
        conn.close()
        system_log('导入日志文件：' + file.filename + '，成功导入 ' + str(success_count) + ' 条记录')
        return { 'status': 'ok', 'data': '导入成功' }
    except Exception as e:
        print("Error: ", e)
        conn.rollback()
        conn.close()
        return { 'status': 'error', 'data': '导入失败：' + str(e) }



@clients_bp.route('/api/get_clients_brief', methods=['GET'])
@jwt_required()
def get_clients_brief():
    return { 'status': 'ok', 'data': fetchall("SELECT client_id, client_name FROM clients") }

@clients_bp.route('/api/update_client', methods=['POST'])
@jwt_required()
def update_client():
    id = request.json.get('id')
    #client_id = request.json.get('client_id')
    client_name = request.json.get('client_name')
    client_ip = request.json.get('client_ip')
    client_type = request.json.get('client_type')
    client_info = request.json.get('client_info')
    model = request.json.get('model')
    port = request.json.get('port', '80')
    username = request.json.get('username')
    password = request.json.get('password')
    api_enable = request.json.get('api_enable')
    if port != None and port!= '':
        port = int(port)
    else:
        port = 80
    if client_ip != None and client_ip != '' and is_valid_ip_or_domain(client_ip) == False:
        return { 'status': 'error', 'data': 'IP地址或域名格式不正确' }
    client = fetchone("SELECT client_id FROM clients WHERE id = ?", (id,))
    if client == None:
        return { 'status': 'error', 'data': '设备不存在' }
    client_id = client['client_id']
    execute("UPDATE clients SET client_name = ?, client_ip = ?, client_type = ?, client_info = ? WHERE id = ?", (client_name, client_ip, client_type, client_info, id))
    # 检查设备信息表中有没有该设备的信息，如果没有则添加
    if fetchvalue("SELECT COUNT(*) FROM client_infos WHERE client_id = ?", (client_id,)) == 0:
        execute("INSERT INTO client_infos (client_id, model, ip, port, username, password, api_enable) VALUES (?, ?, ?, ?, ?, ?, ?)", (client_id, model, client_ip, port, username, password, api_enable))
    else:
        execute("UPDATE client_infos SET model =?, ip =?, port =?, username =?, password =?, api_enable =? WHERE client_id =?", (model, client_ip, port, username, password, api_enable, client_id))
    return { 'status': 'ok', 'data': 'success' }

@clients_bp.route('/api/add_client', methods=['POST'])
@jwt_required()
def add_client():
    client_id = ''
    while True:
        client_id = generate_random_string()
        if fetchvalue("SELECT COUNT(*) FROM clients WHERE client_id = ?", (client_id,)) == 0:
            break
    client_name = request.json.get('client_name')
    client_ip = request.json.get('client_ip')
    client_type = request.json.get('client_type')
    client_info = request.json.get('client_info')
    model = request.json.get('model')
    port = request.json.get('port', '80')
    username = request.json.get('username')
    password = request.json.get('password')
    api_enable = request.json.get('api_enable')
    if port != None and port!= '':
        port = int(port)
    else:
        port = 80
    if client_ip != None and client_ip != '' and is_valid_ip_or_domain(client_ip) == False:
        return { 'status': 'error', 'data': 'IP地址或域名格式不正确' }
    id = execute("INSERT INTO clients (client_id, client_name, client_ip, client_type, client_info) VALUES (?, ?, ?, ?, ?)", (client_id, client_name, client_ip, client_type, client_info))
    # 检查设备信息表中有没有该设备的信息，如果没有则添加
    if fetchvalue("SELECT COUNT(*) FROM client_infos WHERE client_id = ?", (client_id,)) == 0:
        execute("INSERT INTO client_infos (client_id, model, ip, port, username, password, api_enable) VALUES (?, ?, ?, ?, ?, ?, ?)", (client_id, model, client_ip, port, username, password, api_enable))
    else:
        execute("UPDATE client_infos SET model =?, ip =?, port =?, username =?, password =?, api_enable =? WHERE client_id =?", (model, client_ip, port, username, password, api_enable, client_id))
    system_log("添加设备: %s" % client_id)
    device_log(client_id, "设备已添加")
    return { 'status': 'ok', 'data': id }

@clients_bp.route('/api/delete_client', methods=['POST'])
@jwt_required()
def delete_client():
    id = request.json.get('id')
    client_id = fetchvalue('SELECT client_id FROM clients WHERE id = ?', (id,))
    execute("DELETE FROM clients WHERE id = ?", (id,))
    execute("DELETE FROM client_infos WHERE client_id =?", (client_id,))
    execute("DELETE FROM tasks WHERE client_id = ?", (client_id,))
    system_log("删除设备: %s" % client_id)
    device_log(client_id, "设备已删除")
    return { 'status': 'ok', 'data': 'success' }


